<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE topic PUBLIC "-//OASIS//DTD DITA Topic//EN" "topic.dtd">
<topic id="topic_th5_5bf_jr">
  <title>Encrypting Data and Database Connections</title>
  <shortdesc>Describes how to encrypt data at rest in the database or in transit over the network,
    to protect from eavesdroppers or man-in-the-middle attacks.</shortdesc>
  <body>
    <ul id="ul_wxd_dxc_lr">
      <li>Connections between clients and the master database can be encrypted with SSL. This is
        enabled with the <codeph>ssl</codeph> server configuration parameter, which is
          <codeph>off</codeph> by default. Setting the <codeph>ssl</codeph> parameter to
          <codeph>on</codeph> allows client communications with the master to be encrypted. The
        master database must be set up for SSL. See <xref
          href="Authenticate.xml#topic_fzv_wb2_jr/openssl_config"/> for more about encrypting client
        connections with SSL.</li>
      <li>Greenplum Database allows SSL encryption of data in transit between the Greenplum parallel
        file distribution server, <codeph>gpfdist</codeph>, and segment hosts. See <xref
          href="#gpfdist_connections" format="dita"/> for more information. </li>
      <li>The <codeph>pgcrypto</codeph> module of encryption/decryptions functions protect data at
        rest in the database. Encryption at the column level protects sensitive information, such as
        social security numbers or credit card numbers. See <xref href="#pgcrypto" format="dita"/>
        for more information. </li>
    </ul>
  </body>
  <topic id="gpfdist_connections">
    <title>Encrypting gpfdist Connections</title>
    <body>
      <p>The <codeph>gpfdists</codeph> protocol is a secure version of the <codeph>gpfdist</codeph>
        protocol that securely identifies the file server and the Greenplum Database and encrypts
        the communications between them. Using <codeph>gpfdists</codeph> protects against
        eavesdropping and man-in-the-middle attacks.</p>
      <p> The <codeph>gpfdists</codeph> protocol implements client/server SSL security with the
        following notable features: </p>
      <ul id="ul_yyk_vq4_kr">
        <li>Client certificates are required. </li>
        <li>Multilingual certificates are not supported. </li>
        <li>A Certificate Revocation List (CRL) is not supported. </li>
        <li>The TLSv1 protocol is used with the <codeph>TLS_RSA_WITH_AES_128_CBC_SHA</codeph>
          encryption algorithm. These SSL parameters cannot be changed. </li>
        <li>SSL renegotiation is supported. </li>
        <li>The SSL ignore host mismatch parameter is set to false. </li>
        <li>Private keys containing a passphrase are not supported for the <codeph>gpfdist</codeph>
          file server (server.key) or for the Greenplum Database (client.key). </li>
        <li>It is the user's responsibility to issue certificates that are appropriate for the
          operating system in use. Generally, converting certificates to the required format is
          supported, for example using the SSL Converter at <xref
            href="http://www.commoncriteriaportal.org/products/?expand#ALL" format="html"
            scope="external">https://www.sslshopper.com/ssl-converter.html</xref>. </li>
      </ul>
      <p>A <codeph>gpfdist</codeph> server started with the <codeph>--ssl</codeph> option can only
        communicate with the <codeph>gpfdists</codeph> protocol. A <codeph>gpfdist</codeph> server
        started without the <codeph>--ssl</codeph> option can only communicate with the
          <codeph>gpfdist</codeph> protocol. For more detail about <codeph>gpfdist</codeph> refer to
        the <i>Greenplum Database Administrator Guide</i>.</p>
      <p>There are two ways to enable the <codeph>gpfdists</codeph> protocol: <ul id="ul_xdc_ycd_lr">
          <li>Run <codeph>gpfdist</codeph> with the <codeph>--ssl</codeph> option and then use the
              <codeph>gpfdists</codeph> protocol in the <codeph>LOCATION</codeph> clause of a
              <codeph>CREATE EXTERNAL TABLE</codeph> statement. </li>
          <li> Use a YAML control file with the SSL option set to true and run
              <codeph>gpload</codeph>. Running <codeph>gpload</codeph> starts the
              <codeph>gpfdist</codeph> server with the <codeph>--ssl</codeph> option and then uses
            the <codeph>gpfdists</codeph> protocol. </li>
        </ul></p>
      <p> When using gpfdists, the following client certificates must be located in the
          <codeph>$PGDATA/gpfdists</codeph> directory on each segment:</p>
      <ul id="ul_afc_bdd_lr">
        <li>The client certificate file, <codeph>client.crt</codeph>
        </li>
        <li>The client private key file, <codeph>client.key</codeph>
        </li>
        <li>The trusted certificate authorities, <codeph>root.crt</codeph>
        </li>
      </ul>
      <note type="important"> Do not protect the private key with a passphrase. The server does not
        prompt for a passphrase for the private key, and loading data fails with an error if one is
        required. </note>
      <p>When using <codeph>gpload</codeph> with SSL you specify the location of the server
        certificates in the YAML control file. When using <codeph>gpfdist</codeph> with SSL, you
        specify the location of the server certificates with the --ssl option. </p>
      <p>The following example shows how to securely load data into an external table. The example
        creates a readable external table named <codeph>ext_expenses</codeph> from all files with
        the <codeph>txt</codeph> extension, using the <codeph>gpfdists</codeph> protocol. The files
        are formatted with a pipe (<codeph>|</codeph>) as the column delimiter and an empty space as
        null. <ol id="ol_wwy_j2d_lr">
          <li>Run <codeph>gpfdist</codeph> with the <codeph>--ssl</codeph> option on the segment
            hosts. </li>
          <li>Log into the database and run the following
            command:<codeblock>
=# CREATE EXTERNAL TABLE ext_expenses 
   ( name text, date date, amount float4, category text, desc1 text )
LOCATION ('gpfdists://etlhost-1:8081/*.txt', 'gpfdists://etlhost-2:8082/*.txt')
FORMAT 'TEXT' ( DELIMITER '|' NULL ' ') ;
</codeblock></li>
        </ol></p>
    </body>
  </topic>
  <topic id="pgcrypto">
    <title>Encrypting Data at Rest with pgcrypto</title>
    <body>
      <p>The pgcrypto module for Greenplum Database provides functions for encrypting data at rest
        in the database. Administrators can encrypt columns with sensitive information, such as
        social security numbers or credit card numbers, to provide an extra layer of protection.
        Database data stored in encrypted form cannot be read by users who do not have the
        encryption key, and the data cannot be read directly from disk. </p>
      <p>pgcrypto is installed by default when you install Greenplum Database. You must
        explicitly enable pgcrypto in each database in which you want to use the module.</p>
      <p>pgcrypto allows PGP encryption using symmetric and asymmetric encryption. Symmetric
        encryption encrypts and decrypts data using the same key and is faster than asymmetric
        encryption. It is the preferred method in an environment where exchanging secret keys is not
        an issue. With asymmetric encryption, a public key is used to encrypt data and a private key
        is used to decrypt data. This is slower then symmetric encryption and it requires a stronger
        key. </p>
      <p>Using pgcrypto always comes at the cost of performance and maintainability. It is important
        to use encryption only with the data that requires it. Also, keep in mind that you cannot
        search encrypted data by indexing the data. </p>
      <p>Before you implement in-database encryption, consider the following PGP limitations.<ul
          id="ul_ihq_xt4_kr">
          <li> No support for signing. That also means that it is not checked whether the encryption
            sub-key belongs to the master key.</li>
          <li> No support for encryption key as master key. This practice is generally discouraged,
            so this limitation should not be a problem.</li>
          <li> No support for several subkeys. This may seem like a problem, as this is common
            practice. On the other hand, you should not use your regular GPG/PGP keys with pgcrypto,
            but create new ones, as the usage scenario is rather different.</li>
        </ul></p>
      <p>Greenplum Database is compiled with zlib by default; this allows PGP encryption functions
        to compress data before encrypting. When compiled with OpenSSL, more algorithms will be
        available. </p>
      <p>Because pgcrypto functions run inside the database server, the data and passwords move
        between pgcrypto and the client application in clear-text. For optimal security, you should
        connect locally or use SSL connections and you should trust both the system and database
        administrators. </p>
      <p>pgcrypto configures itself according to the findings of the main PostgreSQL configure
        script.</p>
      <p>When compiled with <codeph>zlib</codeph>, pgcrypto encryption functions are able to
        compress data before encrypting. </p>
      <p>Pgcrypto has various levels of encryption ranging from basic to advanced built-in
        functions. The following table shows the supported encryption algorithms.</p>
      <table id="table_wzb_4fd_lr">
        <title>Pgcrypto Supported Encryption Functions</title>
        <tgroup cols="4">
          <colspec colwidth="33*" align="left"/>
          <colspec colwidth="33*" align="left"/>
          <colspec colwidth="33*" align="left"/>
          <thead>
            <row>
              <entry> Value Functionality </entry>
              <entry> Built-in </entry>
              <entry> With OpenSSL </entry>
            </row>
          </thead>
          <tbody>
            <row>
              <entry> MD5 </entry>
              <entry> yes </entry>
              <entry> yes </entry>
            </row>
            <row>
              <entry> SHA1 </entry>
              <entry> yes </entry>
              <entry> yes </entry>
            </row>
            <row>
              <entry> SHA224/256/384/512 </entry>
              <entry> yes </entry>
              <entry> yes <fn>SHA2 algorithms were added to OpenSSL in version 0.9.8. For older
                  versions, pgcrypto will use built-in code.</fn></entry>
            </row>
            <row>
              <entry> Other digest algorithms </entry>
              <entry> no </entry>
              <entry> yes <fn>Any digest algorithm OpenSSL supports is automatically picked up. This
                  is not possible with ciphers, which need to be supported explicitly.</fn></entry>
            </row>
            <row>
              <entry> Blowfish </entry>
              <entry> yes </entry>
              <entry> yes </entry>
            </row>
            <row>
              <entry> AES </entry>
              <entry> yes </entry>
              <entry> yes<fn>AES is included in OpenSSL since version 0.9.7. For older versions,
                  pgcrypto will use built-in code.</fn></entry>
            </row>
            <row>
              <entry> DES/3DES/CAST5 </entry>
              <entry> no </entry>
              <entry> yes </entry>
            </row>
            <row>
              <entry> Raw Encryption </entry>
              <entry> yes </entry>
              <entry> yes </entry>
            </row>
            <row>
              <entry> PGP Symmetric-Key </entry>
              <entry> yes </entry>
              <entry> yes </entry>
            </row>
            <row>
              <entry> PGP Public Key </entry>
              <entry> yes </entry>
              <entry> yes </entry>
            </row>
          </tbody>
        </tgroup>
      </table>
      <section id="creating_pgp_keys">
        <title>Creating PGP Keys</title>
        <p>To use PGP asymmetric encryption in Greenplum Database, you must first create public and
          private keys and install them. </p>
        <p>This section assumes you are installing Greenplum Database on a Linux machine with the
          Gnu Privacy Guard (<codeph>gpg</codeph>) command line tool. Use the latest version of GPG
          to create keys. Download and install Gnu Privacy Guard (GPG) for your operating system
          from <xref href="https://www.gnupg.org/download/" format="html" scope="external"/>. On the
          GnuPG website you will find installers for popular Linux distributions and links for
          Windows and Mac OS X installers. </p>
        <ol id="ol_vnm_h54_kr">
          <li>As root, run the following command and choose option 1 from the
            menu:<codeblock># gpg --gen-key 
gpg (GnuPG) 2.0.14; Copyright (C) 2009 Free Software Foundation, Inc.
This is free software: you are free to change and redistribute it.
There is NO WARRANTY, to the extent permitted by law.
 
gpg: directory '/root/.gnupg' created
gpg: new configuration file '/root/.gnupg/gpg.conf' created
gpg: WARNING: options in '/root/.gnupg/gpg.conf' are not yet active during this run
gpg: keyring '/root/.gnupg/secring.gpg' created
gpg: keyring '/root/.gnupg/pubring.gpg' created
Please select what kind of key you want:
 (1) RSA and RSA (default)
 (2) DSA and Elgamal
 (3) DSA (sign only)
 (4) RSA (sign only)
Your selection? <b>1</b></codeblock></li>
          <li>Respond to the prompts and follow the instructions, as shown in this
            example:<codeblock>RSA keys may be between 1024 and 4096 bits long.
What keysize do you want? (2048) Press enter to accept default key size
Requested keysize is 2048 bits
Please specify how long the key should be valid.
 0 = key does not expire
 &lt;n&gt; = key expires in n days
 &lt;n&gt;w = key expires in n weeks
 &lt;n&gt;m = key expires in n months
 &lt;n&gt;y = key expires in n years
 Key is valid for? (0) <b>365</b>
Key expires at Wed 13 Jan 2016 10:35:39 AM PST
Is this correct? (y/N) <b>y</b>

GnuPG needs to construct a user ID to identify your key.

Real name: <b>John Doe</b>
Email address: <b>jdoe@email.com</b>
Comment: 
You selected this USER-ID:
 "John Doe &lt;jdoe@email.com&gt;"

Change (N)ame, (C)omment, (E)mail or (O)kay/(Q)uit? <b>O</b>
You need a Passphrase to protect your secret key.
<i>(For this demo the passphrase is blank.)</i>
can't connect to '/root/.gnupg/S.gpg-agent': No such file or directory
You don't want a passphrase - this is probably a *bad* idea!
I will do it anyway.  You can change your passphrase at any time,
using this program with the option "--edit-key".

We need to generate a lot of random bytes. It is a good idea to perform
some other action (type on the keyboard, move the mouse, utilize the
disks) during the prime generation; this gives the random number
generator a better chance to gain enough entropy.
We need to generate a lot of random bytes. It is a good idea to perform
some other action (type on the keyboard, move the mouse, utilize the
disks) during the prime generation; this gives the random number
generator a better chance to gain enough entropy.
gpg: /root/.gnupg/trustdb.gpg: trustdb created
gpg: key 2027CC30 marked as ultimately trusted
public and secret key created and signed.

gpg:  checking the trustdbgpg: 
      3 marginal(s) needed, 1 complete(s) needed, PGP trust model
gpg:  depth: 0  valid:   1  signed:   0  trust: 0-, 0q, 0n, 0m, 0f, 1u
gpg:  next trustdb check due at 2016-01-13
pub   2048R/2027CC30 2015-01-13 [expires: 2016-01-13]
      Key fingerprint = 7EDA 6AD0 F5E0 400F 4D45   3259 077D 725E 2027 CC30
uid                  John Doe &lt;jdoe@email.com>
sub   2048R/4FD2EFBB 2015-01-13 [expires: 2016-01-13]
</codeblock></li>
          <li>List the PGP keys by entering the following
              command:<codeblock>gpg --list-secret-keys 
/root/.gnupg/secring.gpg
------------------------
sec   2048R/2027CC30 2015-01-13 [expires: 2016-01-13]
uid                  John Doe &lt;jdoe@email.com&gt;
ssb   2048R/4FD2EFBB 2015-01-13</codeblock><p>2027CC30
              is the public key and will be used to <i>encrypt</i> data in the database. 4FD2EFBB is
              the private (secret) key and will be used to <i>decrypt</i> data.</p></li>
          <li>Export the keys using the following
            commands:<codeblock># gpg -a --export 4FD2EFBB &gt; public.key
# gpg -a --export-secret-keys 2027CC30 &gt; secret.key</codeblock></li>
        </ol>
        <p>See the <xref href="https://www.postgresql.org/docs/9.4/pgcrypto.html"
            format="html" scope="external">pgcrypto</xref> documentation for more information about
          PGP encryption functions. </p>
      </section>
      <section>
        <title>Encrypting Data in Tables using PGP</title>
        <p>This section shows how to encrypt data inserted into a column using the PGP keys you
          generated. </p>
        <ol id="ol_nty_qzd_lr">
          <li>Dump the contents of the <codeph>public.key</codeph> file and then copy it to the
            clipboard:<codeblock># cat public.key
-----BEGIN PGP PUBLIC KEY BLOCK-----
Version: GnuPG v2.0.14 (GNU/Linux)
            
mQENBFS1Zf0BCADNw8Qvk1V1C36Kfcwd3Kpm/dijPfRyyEwB6PqKyA05jtWiXZTh
2His1ojSP6LI0cSkIqMU9LAlncecZhRIhBhuVgKlGSgd9texg2nnSL9Admqik/yX
R5syVKG+qcdWuvyZg9oOOmeyjhc3n+kkbRTEMuM3flbMs8shOwzMvstCUVmuHU/V
vG5rJAe8PuYDSJCJ74I6w7SOH3RiRIc7IfL6xYddV42l3ctd44bl8/i71hq2UyN2
/Hbsjii2ymg7ttw3jsWAx2gP9nssDgoy8QDy/o9nNqC8EGlig96ZFnFnE6Pwbhn+
ic8MD0lK5/GAlR6Hc0ZIHf8KEcavruQlikjnABEBAAG0HHRlc3Qga2V5IDx0ZXN0
a2V5QGVtYWlsLmNvbT6JAT4EEwECACgFAlS1Zf0CGwMFCQHhM4AGCwkIBwMCBhUI
AgkKCwQWAgMBAh4BAheAAAoJEAd9cl4gJ8wwbfwH/3VyVsPkQl1owRJNxvXGt1bY
7BfrvU52yk+PPZYoes9UpdL3CMRk8gAM9bx5Sk08q2UXSZLC6fFOpEW4uWgmGYf8
JRoC3ooezTkmCBW8I1bU0qGetzVxopdXLuPGCE7hVWQe9HcSntiTLxGov1mJAwO7
TAoccXLbyuZh9Rf5vLoQdKzcCyOHh5IqXaQOT100TeFeEpb9TIiwcntg3WCSU5P0
DGoUAOanjDZ3KE8Qp7V74fhG1EZVzHb8FajR62CXSHFKqpBgiNxnTOk45NbXADn4
eTUXPSnwPi46qoAp9UQogsfGyB1XDOTB2UOqhutAMECaM7VtpePv79i0Z/NfnBe5
AQ0EVLVl/QEIANabFdQ+8QMCADOipM1bF/JrQt3zUoc4BTqICaxdyzAfz0tUSf/7
Zro2us99GlARqLWd8EqJcl/xmfcJiZyUam6ZAzzFXCgnH5Y1sdtMTJZdLp5WeOjw
gCWG/ZLu4wzxOFFzDkiPv9RDw6e5MNLtJrSp4hS5o2apKdbO4Ex83O4mJYnav/rE
iDDCWU4T0lhv3hSKCpke6LcwsX+7liozp+aNmP0Ypwfi4hR3UUMP70+V1beFqW2J
bVLz3lLLouHRgpCzla+PzzbEKs16jq77vG9kqZTCIzXoWaLljuitRlfJkO3vQ9hO
v/8yAnkcAmowZrIBlyFg2KBzhunYmN2YvkUAEQEAAYkBJQQYAQIADwUCVLVl/QIb
DAUJAeEzgAAKCRAHfXJeICfMMOHYCACFhInZA9uAM3TC44l+MrgMUJ3rW9izrO48
WrdTsxR8WkSNbIxJoWnYxYuLyPb/shc9k65huw2SSDkj//0fRrI61FPHQNPSvz62
WH+N2lasoUaoJjb2kQGhLOnFbJuevkyBylRz+hI/+8rJKcZOjQkmmK8Hkk8qb5x/
HMUc55H0g2qQAY0BpnJHgOOQ45Q6pk3G2/7Dbek5WJ6K1wUrFy51sNlGWE8pvgEx
/UUZB+dYqCwtvX0nnBu1KNCmk2AkEcFK3YoliCxomdOxhFOv9AKjjojDyC65KJci
Pv2MikPS2fKOAg1R3LpMa8zDEtl4w3vckPQNrQNnYuUtfj6ZoCxv
=XZ8J
-----END PGP PUBLIC KEY BLOCK-----
</codeblock></li>
          <li>Create a table called <codeph>userssn</codeph> and insert some sensitive data, social
            security numbers for Bob and Alice, in this example. Paste the public.key contents after
            "dearmor(".
            <codeblock>CREATE TABLE userssn( ssn_id SERIAL PRIMARY KEY, 
    username varchar(100), ssn bytea); 

INSERT INTO userssn(username, ssn)
SELECT robotccs.username, pgp_pub_encrypt(robotccs.ssn, keys.pubkey) AS ssn
FROM ( 
        VALUES ('Alice', '123-45-6788'), ('Bob', '123-45-6799')) 
            AS robotccs(username, ssn)
CROSS JOIN  (SELECT  dearmor('-----BEGIN PGP PUBLIC KEY BLOCK-----
Version: GnuPG v2.0.14 (GNU/Linux)
            
mQENBFS1Zf0BCADNw8Qvk1V1C36Kfcwd3Kpm/dijPfRyyEwB6PqKyA05jtWiXZTh
2His1ojSP6LI0cSkIqMU9LAlncecZhRIhBhuVgKlGSgd9texg2nnSL9Admqik/yX
R5syVKG+qcdWuvyZg9oOOmeyjhc3n+kkbRTEMuM3flbMs8shOwzMvstCUVmuHU/V
vG5rJAe8PuYDSJCJ74I6w7SOH3RiRIc7IfL6xYddV42l3ctd44bl8/i71hq2UyN2
/Hbsjii2ymg7ttw3jsWAx2gP9nssDgoy8QDy/o9nNqC8EGlig96ZFnFnE6Pwbhn+
ic8MD0lK5/GAlR6Hc0ZIHf8KEcavruQlikjnABEBAAG0HHRlc3Qga2V5IDx0ZXN0
a2V5QGVtYWlsLmNvbT6JAT4EEwECACgFAlS1Zf0CGwMFCQHhM4AGCwkIBwMCBhUI
AgkKCwQWAgMBAh4BAheAAAoJEAd9cl4gJ8wwbfwH/3VyVsPkQl1owRJNxvXGt1bY
7BfrvU52yk+PPZYoes9UpdL3CMRk8gAM9bx5Sk08q2UXSZLC6fFOpEW4uWgmGYf8
JRoC3ooezTkmCBW8I1bU0qGetzVxopdXLuPGCE7hVWQe9HcSntiTLxGov1mJAwO7
TAoccXLbyuZh9Rf5vLoQdKzcCyOHh5IqXaQOT100TeFeEpb9TIiwcntg3WCSU5P0
DGoUAOanjDZ3KE8Qp7V74fhG1EZVzHb8FajR62CXSHFKqpBgiNxnTOk45NbXADn4
eTUXPSnwPi46qoAp9UQogsfGyB1XDOTB2UOqhutAMECaM7VtpePv79i0Z/NfnBe5
AQ0EVLVl/QEIANabFdQ+8QMCADOipM1bF/JrQt3zUoc4BTqICaxdyzAfz0tUSf/7
Zro2us99GlARqLWd8EqJcl/xmfcJiZyUam6ZAzzFXCgnH5Y1sdtMTJZdLp5WeOjw
gCWG/ZLu4wzxOFFzDkiPv9RDw6e5MNLtJrSp4hS5o2apKdbO4Ex83O4mJYnav/rE
iDDCWU4T0lhv3hSKCpke6LcwsX+7liozp+aNmP0Ypwfi4hR3UUMP70+V1beFqW2J
bVLz3lLLouHRgpCzla+PzzbEKs16jq77vG9kqZTCIzXoWaLljuitRlfJkO3vQ9hO
v/8yAnkcAmowZrIBlyFg2KBzhunYmN2YvkUAEQEAAYkBJQQYAQIADwUCVLVl/QIb
DAUJAeEzgAAKCRAHfXJeICfMMOHYCACFhInZA9uAM3TC44l+MrgMUJ3rW9izrO48
WrdTsxR8WkSNbIxJoWnYxYuLyPb/shc9k65huw2SSDkj//0fRrI61FPHQNPSvz62
WH+N2lasoUaoJjb2kQGhLOnFbJuevkyBylRz+hI/+8rJKcZOjQkmmK8Hkk8qb5x/
HMUc55H0g2qQAY0BpnJHgOOQ45Q6pk3G2/7Dbek5WJ6K1wUrFy51sNlGWE8pvgEx
/UUZB+dYqCwtvX0nnBu1KNCmk2AkEcFK3YoliCxomdOxhFOv9AKjjojDyC65KJci
Pv2MikPS2fKOAg1R3LpMa8zDEtl4w3vckPQNrQNnYuUtfj6ZoCxv
=XZ8J
-----END PGP PUBLIC KEY BLOCK-----' AS pubkey) AS keys;
</codeblock></li>
          <li>Verify that the <codeph>ssn</codeph> column is
            encrypted.<codeblock>test_db=# select * from userssn;
ssn_id   | 1
username | Alice
ssn      | \301\300L\003\235M%_O\322\357\273\001\010\000\272\227\010\341\216\360\217C\020\261)_\367
[\227\034\313:C\354d&lt;\337\006Q\351('\2330\031lX\263Qf\341\262\200\3015\235\036AK\242fL+\315g\322
7u\270*\304\361\355\220\021\330"\200%\264\274}R\213\377\363\235\366\030\023)\364!\331\303\237t\277=
f \015\004\242\231\263\225%\032\271a\001\035\277\021\375X\232\304\305/\340\334\0131\325\344[~\362\0
37-\251\336\303\340\377_\011\275\301/MY\334\343\245\244\372y\257S\374\230\346\277\373W\346\230\276\
017fi\226Q\307\012\326\3646\000\326\005:E\364W\252=zz\010(:\343Y\237\257iqU\0326\350=v0\362\327\350\
315G^\027:K_9\254\362\354\215&lt;\001\304\357\331\355\323,\302\213Fe\265\315\232\367\254\245%(\\\373
4\254\230\331\356\006B\257\333\326H\022\013\353\216F?\023\220\370\035vH5/\227\344b\322\227\026\362=\
42\033\322&lt;\001}\243\224;)\030zqX\214\340\221\035\275U\345\327\214\032\351\223c\2442\345\304K\016\
011\214\307\227\237\270\026'R\205\205a~1\263\236[\037C\260\031\205\374\245\317\033k|\366\253\037
---------+--------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------
ssn_id   | 2
username | Bob
ssn      | \301\300L\003\235M%_O\322\357\273\001\007\377t>\345\343,\200\256\272\300\012\033M4\265\032L
L[v\262k\244\2435\264\232B\357\370d9\375\011\002\327\235&lt;\246\210b\030\012\337@\226Z\361\246\032\00
7'\012c\353]\355d7\360T\335\314\367\370;X\371\350*\231\212\260B\010#RQ0\223\253c7\0132b\355\242\233\34
1\000\370\370\366\013\022\357\005i\202~\005\\z\301o\012\230Z\014\362\244\324&amp;\243g\351\362\325\375
\213\032\226$\2751\256XR\346k\266\030\234\267\201vUh\004\250\337A\231\223u\247\366/i\022\275\276\350\2
20\316\306|\203+\010\261;\232\254tp\255\243\261\373Rq;\316w\357\006\207\374U\333\365\365\245hg\031\005
\322\347ea\220\015l\212g\337\264\336b\263\004\311\210.4\340G+\221\274D\035\375\2216\241'\346a0\273wE\2
12\342y^\202\262|A7\202t\240\333p\345G\373\253\243oCO\011\360\247\211\014\024{\272\271\322&lt;\001\267
\347\240\005\213\0078\036\210\307$\317\322\311\222\035\354\006&lt;\266\264\004\376\251q\256\220(+\030\
3270\013c\327\272\212%\363\033\252\322\337\354\276\225\232\201\212^\304\210\2269@\3230\370{
</codeblock></li>
          <li>Extract the public.key ID from the database: <codeblock>SELECT pgp_key_id(dearmor('-----BEGIN PGP PUBLIC KEY BLOCK-----
Version: GnuPG v2.0.14 (GNU/Linux)

mQENBFS1Zf0BCADNw8Qvk1V1C36Kfcwd3Kpm/dijPfRyyEwB6PqKyA05jtWiXZTh
2His1ojSP6LI0cSkIqMU9LAlncecZhRIhBhuVgKlGSgd9texg2nnSL9Admqik/yX
R5syVKG+qcdWuvyZg9oOOmeyjhc3n+kkbRTEMuM3flbMs8shOwzMvstCUVmuHU/V
vG5rJAe8PuYDSJCJ74I6w7SOH3RiRIc7IfL6xYddV42l3ctd44bl8/i71hq2UyN2
/Hbsjii2ymg7ttw3jsWAx2gP9nssDgoy8QDy/o9nNqC8EGlig96ZFnFnE6Pwbhn+
ic8MD0lK5/GAlR6Hc0ZIHf8KEcavruQlikjnABEBAAG0HHRlc3Qga2V5IDx0ZXN0
a2V5QGVtYWlsLmNvbT6JAT4EEwECACgFAlS1Zf0CGwMFCQHhM4AGCwkIBwMCBhUI
AgkKCwQWAgMBAh4BAheAAAoJEAd9cl4gJ8wwbfwH/3VyVsPkQl1owRJNxvXGt1bY
7BfrvU52yk+PPZYoes9UpdL3CMRk8gAM9bx5Sk08q2UXSZLC6fFOpEW4uWgmGYf8
JRoC3ooezTkmCBW8I1bU0qGetzVxopdXLuPGCE7hVWQe9HcSntiTLxGov1mJAwO7
TAoccXLbyuZh9Rf5vLoQdKzcCyOHh5IqXaQOT100TeFeEpb9TIiwcntg3WCSU5P0
DGoUAOanjDZ3KE8Qp7V74fhG1EZVzHb8FajR62CXSHFKqpBgiNxnTOk45NbXADn4
eTUXPSnwPi46qoAp9UQogsfGyB1XDOTB2UOqhutAMECaM7VtpePv79i0Z/NfnBe5
AQ0EVLVl/QEIANabFdQ+8QMCADOipM1bF/JrQt3zUoc4BTqICaxdyzAfz0tUSf/7
Zro2us99GlARqLWd8EqJcl/xmfcJiZyUam6ZAzzFXCgnH5Y1sdtMTJZdLp5WeOjw
gCWG/ZLu4wzxOFFzDkiPv9RDw6e5MNLtJrSp4hS5o2apKdbO4Ex83O4mJYnav/rE
iDDCWU4T0lhv3hSKCpke6LcwsX+7liozp+aNmP0Ypwfi4hR3UUMP70+V1beFqW2J
bVLz3lLLouHRgpCzla+PzzbEKs16jq77vG9kqZTCIzXoWaLljuitRlfJkO3vQ9hO
v/8yAnkcAmowZrIBlyFg2KBzhunYmN2YvkUAEQEAAYkBJQQYAQIADwUCVLVl/QIb
DAUJAeEzgAAKCRAHfXJeICfMMOHYCACFhInZA9uAM3TC44l+MrgMUJ3rW9izrO48
WrdTsxR8WkSNbIxJoWnYxYuLyPb/shc9k65huw2SSDkj//0fRrI61FPHQNPSvz62
WH+N2lasoUaoJjb2kQGhLOnFbJuevkyBylRz+hI/+8rJKcZOjQkmmK8Hkk8qb5x/
HMUc55H0g2qQAY0BpnJHgOOQ45Q6pk3G2/7Dbek5WJ6K1wUrFy51sNlGWE8pvgEx
/UUZB+dYqCwtvX0nnBu1KNCmk2AkEcFK3YoliCxomdOxhFOv9AKjjojDyC65KJci
Pv2MikPS2fKOAg1R3LpMa8zDEtl4w3vckPQNrQNnYuUtfj6ZoCxv
=XZ8J
-----END PGP PUBLIC KEY BLOCK-----'));

pgp_key_id | 9D4D255F4FD2EFBB
</codeblock>
            <p>This shows that the PGP key ID used to encrypt the <codeph>ssn</codeph> column is
              9D4D255F4FD2EFBB. It is recommended to perform this step whenever a new key is created
              and then store the ID for tracking. </p><p>You can use this key to see which key pair
              was used to encrypt the data:</p>
            <codeblock>SELECT username, pgp_key_id(ssn) As key_used
FROM userssn;
username | Bob
key_used | 9D4D255F4FD2EFBB
---------+-----------------
username | Alice
key_used | 9D4D255F4FD2EFBB
</codeblock>
            <note>Different keys may have the same ID. This is rare, but is a normal event. The
              client application should try to decrypt with each one to see which fits — like
              handling <codeph>ANYKEY</codeph>. See <xref
                href="https://www.postgresql.org/docs/9.4/pgcrypto.html" format="html"
                scope="external">pgp_key_id()</xref> in the pgcrypto documentation. </note>
          </li>
          <li>Decrypt the data using the private key.
              <codeblock>SELECT username, pgp_pub_decrypt(ssn, keys.privkey) 
                 AS decrypted_ssn FROM userssn
                 CROSS JOIN
                 (SELECT dearmor('-----BEGIN PGP PRIVATE KEY BLOCK-----
Version: GnuPG v2.0.14 (GNU/Linux)

lQOYBFS1Zf0BCADNw8Qvk1V1C36Kfcwd3Kpm/dijPfRyyEwB6PqKyA05jtWiXZTh
2His1ojSP6LI0cSkIqMU9LAlncecZhRIhBhuVgKlGSgd9texg2nnSL9Admqik/yX
R5syVKG+qcdWuvyZg9oOOmeyjhc3n+kkbRTEMuM3flbMs8shOwzMvstCUVmuHU/V
vG5rJAe8PuYDSJCJ74I6w7SOH3RiRIc7IfL6xYddV42l3ctd44bl8/i71hq2UyN2
/Hbsjii2ymg7ttw3jsWAx2gP9nssDgoy8QDy/o9nNqC8EGlig96ZFnFnE6Pwbhn+
ic8MD0lK5/GAlR6Hc0ZIHf8KEcavruQlikjnABEBAAEAB/wNfjjvP1brRfjjIm/j
XwUNm+sI4v2Ur7qZC94VTukPGf67lvqcYZJuqXxvZrZ8bl6mvl65xEUiZYy7BNA8
fe0PaM4Wy+Xr94Cz2bPbWgawnRNN3GAQy4rlBTrvqQWy+kmpbd87iTjwZidZNNmx
02iSzraq41Rt0Zx21Jh4rkpF67ftmzOH0vlrS0bWOvHUeMY7tCwmdPe9HbQeDlPr
n9CllUqBn4/acTtCClWAjREZn0zXAsNixtTIPC1V+9nO9YmecMkVwNfIPkIhymAM
OPFnuZ/Dz1rCRHjNHb5j6ZyUM5zDqUVnnezktxqrOENSxm0gfMGcpxHQogUMzb7c
6UyBBADSCXHPfo/VPVtMm5p1yGrNOR2jR2rUj9+poZzD2gjkt5G/xIKRlkB4uoQl
emu27wr9dVEX7ms0nvDq58iutbQ4d0JIDlcHMeSRQZluErblB75Vj3HtImblPjpn
4Jx6SWRXPUJPGXGI87u0UoBH0Lwij7M2PW7l1ao+MLEA9jAjQwQA+sr9BKPL4Ya2
r5nE72gsbCCLowkC0rdldf1RGtobwYDMpmYZhOaRKjkOTMG6rCXJxrf6LqiN8w/L
/gNziTmch35MCq/MZzA/bN4VMPyeIlwzxVZkJLsQ7yyqX/A7ac7B7DH0KfXciEXW
MSOAJhMmklW1Q1RRNw3cnYi8w3q7X40EAL/w54FVvvPqp3+sCd86SAAapM4UO2R3
tIsuNVemMWdgNXwvK8AJsz7VreVU5yZ4B8hvCuQj1C7geaN/LXhiT8foRsJC5o71
Bf+iHC/VNEv4k4uDb4lOgnHJYYyifB1wC+nn/EnXCZYQINMia1a4M6Vqc/RIfTH4
nwkZt/89LsAiR/20HHRlc3Qga2V5IDx0ZXN0a2V5QGVtYWlsLmNvbT6JAT4EEwEC
ACgFAlS1Zf0CGwMFCQHhM4AGCwkIBwMCBhUIAgkKCwQWAgMBAh4BAheAAAoJEAd9
cl4gJ8wwbfwH/3VyVsPkQl1owRJNxvXGt1bY7BfrvU52yk+PPZYoes9UpdL3CMRk
8gAM9bx5Sk08q2UXSZLC6fFOpEW4uWgmGYf8JRoC3ooezTkmCBW8I1bU0qGetzVx
opdXLuPGCE7hVWQe9HcSntiTLxGov1mJAwO7TAoccXLbyuZh9Rf5vLoQdKzcCyOH
h5IqXaQOT100TeFeEpb9TIiwcntg3WCSU5P0DGoUAOanjDZ3KE8Qp7V74fhG1EZV
zHb8FajR62CXSHFKqpBgiNxnTOk45NbXADn4eTUXPSnwPi46qoAp9UQogsfGyB1X
DOTB2UOqhutAMECaM7VtpePv79i0Z/NfnBedA5gEVLVl/QEIANabFdQ+8QMCADOi
pM1bF/JrQt3zUoc4BTqICaxdyzAfz0tUSf/7Zro2us99GlARqLWd8EqJcl/xmfcJ
iZyUam6ZAzzFXCgnH5Y1sdtMTJZdLp5WeOjwgCWG/ZLu4wzxOFFzDkiPv9RDw6e5
MNLtJrSp4hS5o2apKdbO4Ex83O4mJYnav/rEiDDCWU4T0lhv3hSKCpke6LcwsX+7
liozp+aNmP0Ypwfi4hR3UUMP70+V1beFqW2JbVLz3lLLouHRgpCzla+PzzbEKs16
jq77vG9kqZTCIzXoWaLljuitRlfJkO3vQ9hOv/8yAnkcAmowZrIBlyFg2KBzhunY
mN2YvkUAEQEAAQAH/A7r4hDrnmzX3QU6FAzePlRB7niJtE2IEN8AufF05Q2PzKU/
c1S72WjtqMAIAgYasDkOhfhcxanTneGuFVYggKT3eSDm1RFKpRjX22m0zKdwy67B
Mu95V2Oklul6OCm8dO6+2fmkGxGqc4ZsKy+jQxtxK3HG9YxMC0dvA2v2C5N4TWi3
Utc7zh//k6IbmaLd7F1d7DXt7Hn2Qsmo8I1rtgPE8grDToomTnRUodToyejEqKyI
ORwsp8n8g2CSFaXSrEyU6HbFYXSxZealhQJGYLFOZdR0MzVtZQCn/7n+IHjupndC
Nd2a8DVx3yQS3dAmvLzhFacZdjXi31wvj0moFOkEAOCz1E63SKNNksniQ11lRMJp
gaov6Ux/zGLMstwTzNouI+Kr8/db0GlSAy1Z3UoAB4tFQXEApoX9A4AJ2KqQjqOX
cZVULenfDZaxrbb9Lid7ZnTDXKVyGTWDF7ZHavHJ4981mCW17lU11zHBB9xMlx6p
dhFvb0gdy0jSLaFMFr/JBAD0fz3RrhP7e6Xll2zdBqGthjC5S/IoKwwBgw6ri2yx
LoxqBr2pl9PotJJ/JUMPhD/LxuTcOZtYjy8PKgm5jhnBDq3Ss0kNKAY1f5EkZG9a
6I4iAX/NekqSyF+OgBfC9aCgS5RG8hYoOCbp8na5R3bgiuS8IzmVmm5OhZ4MDEwg
nQP7BzmR0p5BahpZ8r3Ada7FcK+0ZLLRdLmOYF/yUrZ53SoYCZRzU/GmtQ7LkXBh
Gjqied9Bs1MHdNUolq7GaexcjZmOWHEf6w9+9M4+vxtQq1nkIWqtaphewEmd5/nf
EP3sIY0EAE3mmiLmHLqBju+UJKMNwFNeyMTqgcg50ISH8J9FRIkBJQQYAQIADwUC
VLVl/QIbDAUJAeEzgAAKCRAHfXJeICfMMOHYCACFhInZA9uAM3TC44l+MrgMUJ3r
W9izrO48WrdTsxR8WkSNbIxJoWnYxYuLyPb/shc9k65huw2SSDkj//0fRrI61FPH
QNPSvz62WH+N2lasoUaoJjb2kQGhLOnFbJuevkyBylRz+hI/+8rJKcZOjQkmmK8H
kk8qb5x/HMUc55H0g2qQAY0BpnJHgOOQ45Q6pk3G2/7Dbek5WJ6K1wUrFy51sNlG
WE8pvgEx/UUZB+dYqCwtvX0nnBu1KNCmk2AkEcFK3YoliCxomdOxhFOv9AKjjojD
yC65KJciPv2MikPS2fKOAg1R3LpMa8zDEtl4w3vckPQNrQNnYuUtfj6ZoCxv
=fa+6
-----END PGP PRIVATE KEY BLOCK-----') AS privkey) AS keys;

username | decrypted_ssn 
----------+---------------
 Alice    | 123-45-6788
 Bob      | 123-45-6799
(2 rows)

</codeblock><p>If
              you created a key with passphrase, you may have to enter it here. However for the
              purpose of this example, the passphrase is blank. </p></li>
        </ol>
      </section>
      <section>
        <title>Key Management</title>
        <p>Whether you are using symmetric (single private key) or asymmetric (public and private
          key) cryptography, it is important to store the master or private key securely. There are
          many options for storing encryption keys, for example, on a file system, key vault,
          encrypted USB, trusted platform module (TPM), or hardware security module (HSM). </p>
        <p>Consider the following questions when planning for key management:</p>
        <ul id="ul_vqd_sl2_lr">
          <li> Where will the keys be stored?</li>
          <li> When should keys expire?</li>
          <li> How are keys protected?</li>
          <li> How are keys accessed?</li>
          <li> How can keys be recovered and revoked?</li>
        </ul>
        <p>The Open Web Application Security Project (OWASP) provides a very comprehensive <xref
            href="https://www.owasp.org/index.php/Cryptographic_Storage_Cheat_Sheet" format="html"
            scope="external">guide to securing encryption keys</xref>.</p>
      </section>
    </body>
  </topic>
</topic>
